########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert a CSV file into a flat file using a set of tables of regular expressions"""
import re
import csv
import logging

################# Set of regular expression macros used to build templates #################
def eurdtzre(name):
	"""regular expression for an European Date + Time + TZ
	For example: '15.03.2007 / 21:46 CET'
	'11.04.2007 / 11:49 CEST'
	"""
	return '(?P<%s_day>\d\d)\.(?P<%s_month>\d\d)\.(?P<%s_year>\d{4}) / (?P<%s_hours>\d\d):(?P<%s_min>\d\d) (?P<%s_TZ>[A-Z]{3,4})'%(name,name,name,name,name,name)

def strre(name):
	"""regular expression for a string"""
	return '(?P<%s>.*)'%name

def currencyre(name):
	"""regular expression for currency
	For example: 'USD'
	"""
	return '(?P<%s>[A-Z]{3})'%name

def commanumre(name):
	"""regular expression for a number with commas
	For example: '9,894.97', '0', '-120.00'
	"""
	return '(?P<%s>((\-?\d{1,3}(,\d{3})*\.\d\d)|0))'%name

def commanumreint(name):
	"""regular expression for a number with commas
	For example: '9,894.97', '0', '-120.00'
	"""
	return '(?P<%s>((\-?\d{1,3}(,\d{3})*(\.\d\d)?)|0))'%name

def commaintre(name):
	"""regular expression for an integer with commas
	For example: '9,894', '0', '-120'
	"""
	return '(?P<%s>((\-?\d{1,3}(,\d{3})*)|0))'%name

def pvaluere(name,sig=2):
	"""regular expression for a positive number with cents
	'0.00', '100.12', '9894.97'
	"""
	return '(?P<%s>\d+\.\d{%d,2}|0)'%(name,sig)

def pvaluereint(name,sig=2):
	"""regular expression for a positive number with cents
	'0.00', '100.12', '9894.97'
	"""
	return '(?P<%s>\d+(\.\d{%d,2})?|0)'%(name,sig)

def valuere(name,sig=2):
	"""regular expression for a number with cents
	'0.00', '100.12', '9894.97'
	"""
	return '(?P<%s>\-?\d+\.\d{%d,2}|0)'%(name,sig)

def valuereint(name,sig=2):
	"""regular expression for a number with cents
	'0.00', '100.12', '9894.97'
	"""
	return '(?P<%s>\-?\d+(\.\d{%d,2})?|0)'%(name,sig)

def valuere_optionalcomma(name,sig=2):
	"""regular expression for a number with cents
	'0.00', '100.12', '9894.97', '9,894.97'
	"""
	return '(?P<%s>\-?((\d{1,3}(,\d{3})*)|(\d+))\.\d{%d,2}|0)'%(name,sig)

def pintre(name):
	"""regular expression for an int number
	"""
	return '(?P<%s>\d+)'%(name)

def floatre(name):
	"""regular expression for a float number
	"""
	return '(?P<%s>\-?\d+(\.\d*)?)'%(name)

def eurdre(name,sep='.',year_len=4):
	"""regular expression for an European date
	'20.02.2007'
	"""
	return '(?P<%s_day>\d\d)\%s(?P<%s_month>\d\d)\%s(?P<%s_year>\d{%d})'%(name,sep,name,sep,name,year_len)

def usdre(name,sep='/',year_len=2):
	"""regular expression for a US date
	'12/29/06'
	"""
	return '(?P<%s_month>\d\d)\%s(?P<%s_day>\d\d)\%s(?P<%s_year>\d{%d})'%(name,sep,name,sep,name,year_len)

def empty(name):
	return '(?P<%s>)'%name


##################### Parse a CSV file with fixed structure ######################
def matchline(row,regexps):
	"""Match a list of strings (a parsed line from a CSV file) with a list of regular expressions (template line)
	and return a dictionary of all the named groups found in all matches
	or None if there is no match
	"""
	if len(row)!=len(regexps): return
	objs={}
	for ncell,cell in enumerate(row):
		r=regexps[ncell]
		obj=re.match('\\A'+r+'\\Z',cell)
		if not obj: return
		d=obj.groupdict()
		for k in d:
			if k not in objs or objs[k]==None:
				objs[k]=d[k]
			elif d[k]==None: continue
			elif d[k]!=objs[k]:
				raise Exception("Conflicting match")
	return objs

def fixedtable(reader,regexps):
	"""Read a CSV file and match it to a fixed template made from list of regular expressions for every line in the CSV.
	Return a list of diconaries, one for each line, each dictonary contains all the named groups found.
	"""
	table=[]
	if not regexps: return table
	for nline,row in enumerate(reader):
		regs=regexps[nline]
		objs=matchline(row,regs)
		if objs==None:
			logging.info('%s','ROW: %s'%row)		
			logging.info('%s','REG: %s'%regs)		
			return None
		table.append(objs)
		if nline==len(regexps)-1: return table
	raise Exception('File too short')

def multilinetable(reader,line,footer=None,optional_footer=False):
	"""Read a CSV table using line which is a list of regular expresions that should each match the appropriate cell in each line.
	Repeat the process until EOF or until there is a match with a footer which is an RE of a fixed table
	"""
	table=[]
	footer_table=[]
	footer_line=0
	for row in reader:
		if footer:
			if footer_line>=len(footer): raise Exception('File too long, line %d'%reader.line_num)
			#try:
			footer_objs=matchline(row,footer[footer_line])
			if footer_objs!=None:
				footer_table.append(footer_objs)
				footer_line+=1
				continue
			#except Exception,e:
			else:
				if footer_line:
					#print `e`
					raise Exception('Footer line %d does not match line %d'%(footer_line,reader.line_num))
		assert(footer_line==0) # Entire footer must match once the first line was matched
		objs=matchline(row,line)
		if objs==None:
			logging.info('%s','ROW: %s'%row)		
			logging.info('%s','LIN: %s'%line)		
			raise Exception("Failed to match")
			#return (None,None)
		table.append(objs)
	if footer_line==0 and optional_footer:
		pass
	else:
		if footer and footer_line<len(footer): raise Exception('Only %d lines of footer where found'%footer_line)
	return (table,footer_table)
		
def readcsvtable_multiformat(csvfile,htf_list):
	for version,htf in enumerate(htf_list):

		h,t,f = readcsvtable(csvfile,htf[0],htf[1],htf[2])
		if h!=None: break
	if h==None:
		raise Exception('Unknwon file format')
	return h,t,f,version

def readcsvtable(fname,header=[],line=[],footer=[],optional_footer=False):
	"""Its assumed that the CSV file has a fixed structure:
It starts with a fixed header lines
followed by zero or more lines each with the same structure
and then followed by EOF or a footer line

Return a list of dictonaries for the header, a list of diconaries for the table and a dictonary for the footer.
Each dictonary contains all the named groups that where found in one line.
"""
	reader = csv.reader(open(fname, "rb"))
	h=fixedtable(reader,header)
	if h==None: return (None,None,None)
	t,f = multilinetable(reader,line,footer,optional_footer)
	return (h,t,f)
